import sqlite3

from date_util import DateUtil


class DbUtil:
    """ 数据库工具类 """

    def __init__(self):
        car_infos = [
            ('新A 28970', '2019-10-22 10:00:00', '2019-10-22 19:00:00', 18.00),
            ('新B 12345', '2020-09-09 11:00:00', '2020-09-09 20:00:00', 20.00),
            ('新C 00000', '2020-10-15 15:00:00', '2020-10-15 23:00:00', 15.00),
            ('新D 23650', '2020-11-01 11:00:00', '2020-11-01 12:00:00', 8.00),
            ('新E 28971', '2020-12-01 13:00:00', '2020-12-01 15:00:00', 16.00),
            ('新E 28971', '2020-12-01 19:00:00', '2020-12-01 20:00:00', 16.00),
            ('新E 28971', '2020-12-02 01:00:00', '2020-12-02 13:00:00', 98.00),
        ]
        car_infos_2 = [
            ('新E 28971', '2020-12-03 14:00:00'),
            ('新E 28972', '2020-12-03 18:00:00'),
        ]
        config_data = [
            ('unit_price', '8.00'),
            ('garage_size', '100'),
        ]

        self.conn = sqlite3.connect('parking.db')
        self.cursor = self.conn.cursor()
        sql1 = "drop table if exists car_info"
        sql2 = "create table car_info(" \
               " id integer primary key," \
               " plate_number varchar(10) not null," \
               " start_time timestamp not null," \
               " end_time timestamp," \
               " price decimal(5,2))"
        sql3 = "drop table if exists config"
        sql4 = "create table config(" \
               "   name varchar(10) primary key," \
               "   value varchar(10))"
        self.cursor.execute(sql1)
        self.cursor.execute(sql2)
        self.cursor.execute(sql3)
        self.cursor.execute(sql4)
        self.cursor.executemany("insert into car_info(plate_number, start_time, end_time, price) values(?,?,?,?)",
                                car_infos)
        self.cursor.executemany("insert into car_info(plate_number, start_time) values(?,?)", car_infos_2)
        self.cursor.executemany("insert into config values(?, ?)", config_data)
        self.conn.commit()

    def in_storage(self, plate_number):
        """ 录入车辆进入停车场信息 """
        insert_sql = "insert into car_info values(null, '%s', '%s', null, null)" \
                     % (plate_number, DateUtil.get_current_date_str())
        self.cursor.execute(insert_sql)
        self.conn.commit()

    def car_pay(self, car_info):
        """ 车辆出场，更新支付信息 """
        update_sql = "update car_info set end_time = '%s', price = %f where plate_number = '%s' and start_time = '%s'" \
                     % (car_info.get_end_time(), car_info.get_price(), car_info.get_plate_number(),
                        car_info.get_start_time())
        self.cursor.execute(update_sql)
        self.conn.commit()

    def get_car_info(self, plate_number, start_time):
        """ 根据车牌号和时间查询车辆信息 """
        self.cursor.execute(
            "select * from car_info where plate_number = '%s' and start_time = '%s'" % (plate_number, start_time))
        result = self.cursor.fetchall()
        if result:
            return result

    def get_history(self, plate_number):
        """ 根据车牌号获取该车的历史停留信息 """
        query_sql = "select * from car_info where plate_number = '%s' and end_time is not null" %plate_number
        self.cursor.execute(query_sql)
        result = self.cursor.fetchall()
        if result:
            return result

    def get_car_start_time(self, plate_number):
        """ 根据车牌号查询开始停车时间 """
        self.cursor.execute(
            "select start_time from car_info where plate_number = '%s' and end_time is null" % plate_number)
        result = self.cursor.fetchall()
        if result:
            return result[0][0]

    def get_current_car_amount(self):
        """ 获取当前停车数量 """
        self.cursor.execute("select count(*) from car_info where end_time is not null")
        values = self.cursor.fetchall()
        return int(values[0][0])

    def get_unit_price(self):
        """ 获取停车费单价 """
        self.cursor.execute("select value from config where name = 'unit_price'")
        value = self.cursor.fetchall()
        return float(value[0][0])

    def get_garage_size(self):
        """ 获取停车场大小 """
        self.cursor.execute("select value from config where name = 'garage_size'")
        value = self.cursor.fetchall()
        return int(value[0][0])